<?php
namespace app\cms\controller;
use app\common\base\_Db;
use app\common\entity\RequestEntity;
use think\Controller;
use think\Db;

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/9/7
 * Time: 10:19
 */
class Extertrade extends Controller{
    /*****************驻场职工乘车订单*************************/
    /*
     * 外部公司（除专业化）
     */
    public function ListItem(){
        $request_data = [
            $get_keyword = new RequestEntity('search_key', '搜索时间', false, 0)
        ];
        _Db::Validates($request_data);
        $param_page = input('Page', 1);
        $param_pagesize = 10;
        $where = "iden = 3";
        $start_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
        $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
        $array_data_range = explode('|', $get_keyword->text);
        if(!empty($get_keyword->text)){
            if (is_array($array_data_range) && $array_data_range[0] !== '' && $array_data_range[1] !== '') {
                $where .= " and time between '" . $array_data_range[0] . " 00:00:00'" . " and '" . $array_data_range[1] . " 23:59:59'";
            }
        }else{
            $where .= " and time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
        }
        //$data = Comtrade::DayItem('external_staff b','a.uid = b.id',$where,$param_page);
        $result = Db::table('free_order')
            ->field(
                [
                    'COUNT(a.money)'	 =>'Count',
                    'SUM(a.money)'	     =>'SUM',
                    'b.company_id'	     =>'CompanyId',
                    'f.company_name'     =>'CompanyName',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->where($where)
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->group('CompanyName')
            ->order('a.time desc')
            ->select();
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        //总条数
        $int_total_count = count(Db::table('free_order')
            ->field(
                [
                    'COUNT(a.money)'	 =>'Count',
                    'SUM(a.money)'	     =>'SUM',
                    'b.company_id'	     =>'CompanyId',
                    'f.company_name'     =>'CompanyName',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->where($where)
            ->group('CompanyName')
            ->order('a.time desc')
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        _Db::ResponseJson($data);
    }
    /*
     * 详细信息
     * 每个公司详细情况
     * ListItem->ItemInfo
     */
    public function ItemInfo(){
        //获取请求参数
        $request_data = [
            $post_itemid = new RequestEntity('CompanyId', 'ID', true, 0),
        ];
        _Db::Validates($request_data);
        $param_page = input('Page', 1);
        $param_pagesize = 10;
        $start_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
        $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
        $where = "iden = 3 and time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
        $where .= ' and b.company_id =' . $post_itemid->text;
        $result = Db::table('free_order')
            ->field(
                [
                    'a.id'	             =>'Id',
                    'a.time'	         =>'Time',
                    //'b.company_id'	     =>'CompanyId',
                    'a.money'	         =>'Money',
                    'c.line_from'        =>'LineFrom',
                    'c.line_destination' =>'LineDes',
                    'b.nick_name'        =>'NickName',
                    'd.bus_number'       =>'BusNumber',
                    'f.company_name'     =>'CompanyName',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where)
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->order('a.time desc')
            ->select();
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        //总条数
        $int_total_count = count(Db::table('free_order')
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where)
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        _Db::ResponseJson($data);
    }
    /*
     * 专业化统计
     */
    public function ListSpe(){
        $request_data = [
            $get_keyword = new RequestEntity('search_key', '搜索时间', false, 0)
        ];
        _Db::Validates($request_data);
        $param_page = input('Page', 1);
        $param_pagesize = 10;
        $where = "iden = 3";
        $start_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
        $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
        $array_data_range = explode('|', $get_keyword->text);
        if(!empty($get_keyword->text)){
            if (is_array($array_data_range) && $array_data_range[0] !== '' && $array_data_range[1] !== '') {
                $where .= " and time between '" . $array_data_range[0] . " 00:00:00'" . " and '" . $array_data_range[1] . " 23:59:59'";
            }
        }else{
            $where .= " and time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
        }
        //$data = Comtrade::DayItem('external_staff b','a.uid = b.id',$where,$param_page);
        $result = Db::table('free_order')
            ->field(
                [
                    'COUNT(a.money)'	 =>'Count',
                    'c.line_from'        =>'LineFrom',
                    'c.line_destination' =>'LineDes',
                    'b.nick_name'        =>'NickName',
                    'd.bus_number'       =>'BusNumber',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where.' and f.company_id != 1')
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->group('BusNumber')
            ->order('a.time desc')
            ->select();
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        //总条数
        $int_total_count = count(Db::table('free_order')
            ->field(
                [
                    'COUNT(a.money)'	 =>'Count',
                    'c.line_from'        =>'LineFrom',
                    'c.line_destination' =>'LineDes',
                    'b.nick_name'        =>'NickName',
                    'd.bus_number'       =>'BusNumber',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where.' and f.company_id != 1')
            ->group('BusNumber')
            ->order('a.time desc')
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        _Db::ResponseJson($data);
    }
    /*
     * 详细信息
     * 每个公司详细情况
     * ListSpe->ItemSpe
     */
    public function ItemSpe(){
        //获取请求参数
        $request_data = [
            $post_itemid = new RequestEntity('BusNumber', 'BusNumber', true, 0),
        ];
        _Db::Validates($request_data);
        $param_page = input('Page', 1);
        $param_pagesize = 10;
        $start_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
        $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
        $where = "iden = 3 and time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
        $where .= ' and d.bus_number ="' . $post_itemid->text.'"';
        $result = Db::table('free_order')
            ->field(
                [
                    'a.id'	             =>'Id',
                    'a.money'	         =>'Money',
                    'a.time'	         =>'Time',
                    'c.line_from'        =>'LineFrom',
                    'c.line_destination' =>'LineDes',
                    'b.nick_name'        =>'NickName',
                    'd.bus_number'       =>'BusNumber',
                ]
            )
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where.' and f.company_id != 1')
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->order('a.time desc')
            ->select();
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        //总条数
        $int_total_count = count(Db::table('free_order')
            ->alias('a')
            ->join('external_staff b','a.uid = b.id')
            ->join('external_company f','b.company_id = f.company_id','LEFT')
            ->join('line c','a.line_id = c.line_id','LEFT')
            ->join('bus_info d','a.deviceid = d.vehicle_device_id','LEFT')
            ->where($where.' and f.company_id != 1')
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        _Db::ResponseJson($data);
    }
}